package com.cup.bddm.impala.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.cup.bddm.impala.Constant;
import com.cup.bddm.impala.ImpalaJdbc;
import com.cup.bddm.impala.po.MchntBaseInfo;
import com.cup.bddm.impala.po.MchntCouponInfo;
import com.cup.bddm.impala.po.MchntDailyTrans;
import com.cup.bddm.impala.po.MchntInfo;
import com.cup.bddm.util.DateUtil;
import com.cup.bddm.util.MD5Util;

@Repository("merchantDao")
public class MerchantDao {

    static Logger logger = LoggerFactory.getLogger(MerchantDao.class);

    @Autowired
    private ImpalaJdbc impalaJdbc;

    /** 获取商户基本信息 */
    public MchntInfo getMerchantInfo(String mchntCd) {
        String sql = "SELECT store_id,store_nm,mchnt_tp,store_addr, biz_street_cd,biz_street_nm,cup_branch_ins_id_cd,cup_branch_nm " + "FROM " + Constant.TBL_QRC_MCHNT_INFO + " where row_key>=? and row_key<=?";

        logger.info("exacute sql:" + sql + " . paremeter: store_id=" + mchntCd);

        MchntInfo mchntInfo = null;
        try {
            Map<String, Object> merchantMap = impalaJdbc.queryForMap(sql, mchntCd + "|", mchntCd + "}");

            if (merchantMap != null) {
                mchntInfo = new MchntInfo();
                mchntInfo.setMchnt_cd(merchantMap.get("store_id").toString());
                mchntInfo.setMchnt_nm(merchantMap.get("store_nm").toString());
                mchntInfo.setMchnt_tp(merchantMap.get("mchnt_tp").toString());
                mchntInfo.setCard_accpt_nm_addr(merchantMap.get("store_addr").toString());
                mchntInfo.setStreet_cd(merchantMap.get("biz_street_cd").toString());
                mchntInfo.setStreet_nm(merchantMap.get("biz_street_nm").toString());
                mchntInfo.setCup_branch_ins_id_cd(merchantMap.get("cup_branch_ins_id_cd").toString());
                mchntInfo.setCup_branch_nm(merchantMap.get("cup_branch_nm").toString());

            }

        } catch (SQLException e) {
            logger.error("error in query merchant info", e);
            throw new RuntimeException("error in query merchant info");
        }

        return mchntInfo;
    }

    /** 获取当日商户营销信息 */
    public List<MchntDailyTrans> getMchntAllDailyTrans(String mchntCd, String month) {

        String sql = "select * from " + Constant.TBL_MCHNT_ALL_DAY_TRANS + " where row_key>=? and row_key<=? order by pdate";
        logger.info("exacute sql:" + sql + " . paremeter: mchnt_cd=" + mchntCd + ". month=" + month);
        List<MchntDailyTrans> dailyTransList = new LinkedList<MchntDailyTrans>();

        try {
            List<Map<String, Object>> mapList = impalaJdbc.queryForList(sql, mchntCd + "|" + month + "01", mchntCd + "|" + month + "31");

            for (Map<String, Object> map : mapList) {
                MchntDailyTrans dailyTrans = new MchntDailyTrans();
                dailyTrans.setPdate(map.get("pdate").toString());
                dailyTrans.setTrans_count((BigDecimal) map.get("trans_count"));

                dailyTransList.add(dailyTrans);
            }

        } catch (SQLException e) {
            logger.error("error in query merchant all daily trans ", e);
            throw new RuntimeException("error in query merchant all daily trans info");
        }
        return dailyTransList;
    }

    /** 获取某一天的交易数据 */
    public List<MchntDailyTrans> getMchntNfcDailyTrans(String mchntCd, String month) {
        String sql = "select * from " + Constant.TBL_MCHNT_NFC_DAY_TRANS + " where row_key>=? and row_key<=? order by pdate";
        logger.info("exacute sql:" + sql + " . paremeter: mchnt_cd=" + mchntCd + ". substr(pdate,1,6)=" + month);
        List<MchntDailyTrans> dailyTransList = new LinkedList<MchntDailyTrans>();

        try {
            List<Map<String, Object>> mapList = impalaJdbc.queryForList(sql, mchntCd + "|" + month + "01", mchntCd + "|" + month + "31");

            for (Map<String, Object> map : mapList) {
                MchntDailyTrans dailyTrans = new MchntDailyTrans();

                dailyTrans.setPdate(map.get("pdate").toString());
                dailyTrans.setTrans_count((BigDecimal) map.get("trans_count"));

                dailyTransList.add(dailyTrans);
            }

        } catch (SQLException e) {
            logger.error("error in query merchant nfc daily trans ", e);
            throw new RuntimeException("error in query merchant nfc daily trans info");
        }
        return dailyTransList;
    }

    /** 获取商户每日交易数据 */
    public List<MchntDailyTrans> getMchntQrcDailyTrans(String mchntCd, String month) {
        String sql = "select * from " + Constant.TBL_MCHNT_QRC_DAY_TRANS + " where row_key>=? and row_key<=? order by pdate";

        logger.info("exacute sql:" + sql + " . paremeter: mchnt_cd=" + mchntCd + ". substr(pdate,1,6)=" + month);

        List<MchntDailyTrans> dailyTransList = new LinkedList<MchntDailyTrans>();

        try {
            List<Map<String, Object>> mapList = impalaJdbc.queryForList(sql, mchntCd + "|" + month + "01", mchntCd + "|" + month + "31");

            for (Map<String, Object> map : mapList) {
                MchntDailyTrans dailyTrans = new MchntDailyTrans();

                dailyTrans.setPdate(map.get("pdate").toString());
                dailyTrans.setTrans_count((BigDecimal) map.get("trans_count"));

                dailyTransList.add(dailyTrans);
            }

        } catch (SQLException e) {
            logger.error("error in query merchant qrc daily trans ", e);
            throw new RuntimeException("error in query merchant qrc daily trans info");
        }
        return dailyTransList;
    }

    /** 获取商户每日NFC交易数据 */
    public List<MchntDailyTrans> getMchntOfflineDailyTrans(String mchntCd, String month) {
        String sql = "select * from " + Constant.TBL_MCHNT_OFFLINE_DAY_TRANS + " where row_key>=? and row_key<=? order by pdate";

        logger.info("exacute sql:" + sql + " . paremeter: mchnt_cd=" + mchntCd + ". substr(pdate,1,6)=" + month);

        List<MchntDailyTrans> dailyTransList = new LinkedList<MchntDailyTrans>();

        try {
            List<Map<String, Object>> mapList = impalaJdbc.queryForList(sql, mchntCd + "|" + month + "01", mchntCd + "|" + month + "31");

            for (Map<String, Object> map : mapList) {
                MchntDailyTrans dailyTrans = new MchntDailyTrans();

                dailyTrans.setPdate(map.get("pdate").toString());
                dailyTrans.setTrans_count((BigDecimal) map.get("trans_count"));

                dailyTransList.add(dailyTrans);
            }

        } catch (SQLException e) {
            logger.error("error in query merchant offline daily trans ", e);
            throw new RuntimeException("error in query merchant offline daily trans info");
        }
        return dailyTransList;
    }

    /** 获取商户每日二维码交易数据 */
    public List<String> getTopMchntNfcApp(String mchntCd, String month) {
        String nextMonth = DateUtil.getPreNMonth(month, -1);
        String row_key_start = mchntCd + "|" + month;
        String row_key_end = mchntCd + "|" + nextMonth;
        String sql = "select nfc_cd,nfc_nm, sum(trans_count) as count from " + Constant.TBL_MCHNT_NFC_DETAIL_DAY_TRANS + " where row_key>= ? AND row_key< ?" + " group by nfc_cd,nfc_nm order by count desc limit 5 ";

        logger.info("exacute sql:" + sql + " . paremeter: row_key_start=" + row_key_start + ". row_key_end=" + row_key_end);

        List<String> appList = new ArrayList<String>();
        try {
            List<Map<String, Object>> mapList = impalaJdbc.queryForList(sql, row_key_start, row_key_end);

            for (Map<String, Object> map : mapList) {

                String appName = map.get("nfc_nm").toString();

                appList.add(appName);
            }

        } catch (SQLException e) {
            logger.error("error in query merchant top nfc app ", e);
            throw new RuntimeException("error in query merchant top nfc app");
        }

        return appList;
    }

    /** 获取商户线下交易数据 */
    public List<String> getTopMchntQrcApp(String mchntCd, String month) {
        String sql = "select app_ins_id_cd,app_ins_id_nm, sum(trans_count) as count from " + Constant.TBL_MCHNT_APP_DAY_TRANS + " where row_key>=? and row_key<? group by  app_ins_id_cd,app_ins_id_nm order by count desc limit 5 ";
        logger.info("exacute sql:" + sql + " . paremeter: mchnt_cd=" + mchntCd + ". substr(pdate,1,6)=" + month);

        List<String> appList = new ArrayList<String>();
        try {
            List<Map<String, Object>> mapList = impalaJdbc.queryForList(sql, mchntCd + "|" + month, mchntCd + "|" + DateUtil.getPreNMonth(month, -1));

            for (Map<String, Object> map : mapList) {

                String appName = map.get("app_ins_id_nm").toString();

                appList.add(appName);
            }

        } catch (SQLException e) {
            logger.error("error in query merchant top qrc app ", e);
            throw new RuntimeException("error in query merchant top qrc app ");
        }

        return appList;
    }

    /** 获取当月手机pay交易TOP5手机厂商 */
    public Integer getNfcRank(String streetCd, String mchntCd, String date) {
        String nextDay = DateUtil.getNextDay(date);
        String row_key_start = streetCd + "|" + date;
        String row_key_end = streetCd + "|" + nextDay;
        String sql = "SELECT t.number from (SELECT row_number() OVER (ORDER BY trans_count desc) number,mchnt_cd from " + Constant.TBL_MCHNT_NFC_DAY_TRANS + " WHERE  row_key>=? and row_key<? )t where t.mchnt_cd =?";

        logger.info("exacute sql:" + sql + " . paremeter: " + "row_key_start=" + row_key_start + ". row_key_end=" + row_key_end + ". mchntCd=" + mchntCd);

        Integer rank = null;
        try {
            Map<String, Object> result = impalaJdbc.queryForMap(sql, row_key_start, row_key_end, mchntCd);
            if (result != null) {
                Long longRank = (Long) result.get("number");
                rank = longRank.intValue();
            }

        } catch (SQLException e) {
            logger.error("error in query nfc rank", e);
            throw new RuntimeException("error in query nfc rank");

        }
        return rank;
    }

    /** 获取当月二维码交易TOP5APP */
    public Integer getQrcRank(String streetCd, String mchntCd, String date) {
        String nextDay = DateUtil.getNextDay(date);
        String row_key_start = streetCd + "|" + date;
        String row_key_end = streetCd + "|" + nextDay;
        String sql = "SELECT t.number from (SELECT row_number() OVER (ORDER BY trans_count desc) number,mchnt_cd from " + Constant.TBL_MCHNT_QRC_DAY_TRANS + " WHERE  row_key>=? and row_key<? )t where t.mchnt_cd =?";

        logger.info("exacute sql:" + sql + " . paremeter: " + "row_key_start=" + row_key_start + ". row_key_end=" + row_key_end + ". mchntCd=" + mchntCd);

        Integer rank = null;
        try {
            Map<String, Object> result = impalaJdbc.queryForMap(sql, row_key_start, row_key_end, mchntCd);
            if (result != null) {
                Long longRank = (Long) result.get("number");
                rank = longRank.intValue();
            }

        } catch (SQLException e) {
            logger.error("error in query qrc rank", e);
            throw new RuntimeException("error in query qrc rank");

        }
        return rank;
    }

    /** 获取商圈内nfc交易排名 */
    public Integer getOfflineRank(String streetCd, String mchntCd, String date) {
        String nextDay = DateUtil.getNextDay(date);
        String row_key_start = streetCd + "|" + date;
        String row_key_end = streetCd + "|" + nextDay;
        String sql = "SELECT t.number from (SELECT row_number() OVER (ORDER BY trans_count desc) number,mchnt_cd from " + Constant.TBL_MCHNT_OFFLINE_DAY_TRANS + " WHERE  row_key>=? and row_key<? )t where t.mchnt_cd =?";

        logger.info("exacute sql:" + sql + " . paremeter: " + "row_key_start=" + row_key_start + ". row_key_end=" + row_key_end + ". mchntCd=" + mchntCd);

        Integer rank = null;
        try {
            Map<String, Object> result = impalaJdbc.queryForMap(sql, row_key_start, row_key_end, mchntCd);

            if (result != null) {
                Long longRank = (Long) result.get("number");
                rank = longRank.intValue();
            }

        } catch (SQLException e) {
            logger.error("error in query offline rank", e);
            throw new RuntimeException("error in query offline rank");

        }
        return rank;
    }

    /** 获取商圈内二维码交易排名 */
    public List<MchntDailyTrans> getTopMchntInStrByMcc(String streetCd, String mchntTp, String date, String type) {
        String a_row_key_start = streetCd + "|" + date + "|";
        String a_row_key_end = streetCd + "|" + date + "}";
        String m_row_key_start = streetCd + "_";
        String m_row_key_end = streetCd + "}";
        String sql = "";
        if (type.equals("nfc")) {
            sql = "SELECT DISTINCT a.mchnt_cd,a.mchnt_nm,a.trans_count, a.trans_sum from " + Constant.TBL_MCHNT_NFC_DAY_TRANS + " a inner join " + Constant.TBL_QRC_MCHNT_INFO + " m on a.mchnt_cd=m.store_id " + " AND a.row_key>=? and a.row_key<=?" + " AND m.row_key>=? and m.row_key<=?" + " AND m.mchnt_tp =?" + " ORDER BY a.trans_count desc limit 5";

        } else if (type.equals("qrc")) {
            sql = "SELECT DISTINCT a.mchnt_cd,a.mchnt_nm,a.trans_count, a.trans_sum from " + Constant.TBL_MCHNT_QRC_DAY_TRANS + " a inner join " + Constant.TBL_QRC_MCHNT_INFO + " m on a.mchnt_cd=m.store_id" + " AND a.row_key>=? and a.row_key<=?" + " AND m.row_key>=? and m.row_key<=?" + " AND m.mchnt_tp =?" + " ORDER BY a.trans_count desc limit 5";

        } else if (type.equals("offline")) {
            sql = "SELECT DISTINCT a.mchnt_cd,a.mchnt_nm,a.trans_count, a.trans_sum from " + Constant.TBL_MCHNT_OFFLINE_DAY_TRANS + " a inner join " + Constant.TBL_QRC_MCHNT_INFO + " m on a.mchnt_cd=m.store_id" + " AND a.row_key>=? and a.row_key<=?" + " AND m.row_key>=? and m.row_key<=?" + " AND m.mchnt_tp =?" + " ORDER BY a.trans_count desc limit 5";
        } else {
            return null;
        }

        logger.info("exacute sql:" + sql + " . paremeter: date=" + date + ". mchntTp=" + mchntTp + ". streetCd=" + streetCd);
        List<MchntDailyTrans> result = new ArrayList<MchntDailyTrans>();
        try {
            List<Map<String, Object>> list = impalaJdbc.queryForList(sql, a_row_key_start, a_row_key_end, m_row_key_start, m_row_key_end, mchntTp);
            for (Map<String, Object> map : list) {
                MchntDailyTrans mchntTrans = new MchntDailyTrans();
                mchntTrans.setMchnt_cd(map.get("mchnt_cd").toString());
                mchntTrans.setMchnt_nm(map.get("mchnt_nm").toString());
                mchntTrans.setTrans_count((BigDecimal) map.get("trans_count"));
                mchntTrans.setTrans_sum((BigDecimal) map.get("trans_sum"));
                result.add(mchntTrans);
            }
        } catch (SQLException e) {
            logger.error("error in query top merchant in street ", e);
            throw new RuntimeException("error in query top merchant in street ");
        }
        return result;
    }

    /** 获取商圈内线下交易排名 */
    public String getRecentDate() {
        String sql = "select max(pdate) as rec_date from " + Constant.TBL_MCHNT_ALL_DAY_TRANS;
        String date = "";
        try {
            Map<String, Object> result = impalaJdbc.queryForMap(sql);
            if (result != null) {
                date = (String) result.get("rec_date");
            }

        } catch (SQLException e) {
            logger.error("error in query recent date", e);
            throw new RuntimeException("error in query recent date");

        }
        return date;
    }

    /** 获取当日商圈内同业商户交易排名TOP5 */
    public List<MchntCouponInfo> getMchntCouponInfo(String mchntCd, String date) {
        String sql = "select * from " + Constant.TBL_MCHNT_COUPON_INFO + " where row_key=?";
        logger.info("exacute sql:" + sql + " . paremeter: mchnt_cd=" + mchntCd + ". pdate=" + date);

        List<MchntCouponInfo> result = new ArrayList<MchntCouponInfo>();

        try {
            List<Map<String, Object>> list = impalaJdbc.queryForList(sql, mchntCd + "|" + date);
            for (Map<String, Object> map : list) {
                MchntCouponInfo couponInfo = new MchntCouponInfo();
                couponInfo.setPdate(map.get("pdate").toString());
                couponInfo.setMchnt_cd(map.get("mchnt_cd").toString());
                couponInfo.setCoupon_info(map.get("coupon_info").toString());
                result.add(couponInfo);
            }

        } catch (SQLException e) {
            logger.error("error in query merchant coupon info ", e);
            throw new RuntimeException("error in query merchant coupon info");
        }
        return result;
    }

    /** 获取最新数据的日期 */
    public MchntDailyTrans getMchntDayTrans(String mchntCd, String date, String type) {

        String sql = "";
        if (type.equals("all")) {
            sql = "select * from " + Constant.TBL_MCHNT_ALL_DAY_TRANS + " where row_key=?";
        } else if (type.equals("nfc")) {
            sql = "select * from " + Constant.TBL_MCHNT_NFC_DAY_TRANS + " where row_key=?";
        } else if (type.equals("qrc")) {
            sql = "select * from " + Constant.TBL_MCHNT_QRC_DAY_TRANS + " where row_key=?";
        } else if (type.equals("offline")) {
            sql = "select * from " + Constant.TBL_MCHNT_OFFLINE_DAY_TRANS + " where row_key=?";
        } else {
            return null;
        }

        logger.info("exacute sql:" + sql + " . paremeter: mchnt_cd=" + mchntCd + ". pdate=" + date);
        MchntDailyTrans dailyTrans = null;

        try {
            Map<String, Object> map = impalaJdbc.queryForMap(sql, mchntCd + "|" + date);
            if (map != null) {
                dailyTrans = new MchntDailyTrans();
                dailyTrans.setPdate(map.get("pdate").toString());
                dailyTrans.setTrans_count((BigDecimal) map.get("trans_count"));
                dailyTrans.setTrans_sum((BigDecimal) map.get("trans_sum"));
                dailyTrans.setAvg_trans_at((BigDecimal) map.get("avg_trans_at"));
                dailyTrans.setCard_count((BigDecimal) map.get("card_count"));
            } else {
                dailyTrans = new MchntDailyTrans();
                dailyTrans.setPdate(date);
                dailyTrans.setTrans_count(new BigDecimal("0"));
                dailyTrans.setTrans_sum(new BigDecimal("0"));
                dailyTrans.setAvg_trans_at(new BigDecimal("0"));
                dailyTrans.setCard_count(new BigDecimal("0"));
            }
        } catch (SQLException e) {
            logger.error("error in query merchant day trans ", e);
            throw new RuntimeException("error in query merchant  day trans info");
        }
        return dailyTrans;
    }

    /** 更新或新增商户信息 */
    public List<MchntBaseInfo> updateMchntInfo(List<MchntBaseInfo> mchnts) {
        String sql = "insert into " + Constant.TBL_QRC_MCHNT_INFO + "(row_key,biz_street_cd,biz_street_nm,cashier_train_flag,competitor,competitor_coupon,competitor_tp,contact,cup_branch_ins_id_cd,cup_branch_nm,logo_num,logo_post_flag,mchnt_cd,mchnt_tp,nfc_flag,pwd_sign_flag,qrc_flag,scan_tp,store_addr,store_id,store_nm,tel,term_id,trans_at_per_bill,rec_upd_ts,rec_crt_ts) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),now());";

        List<MchntBaseInfo> result = new ArrayList<MchntBaseInfo>();
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = impalaJdbc.getConnection();
            stmt = conn.prepareStatement(sql);
            for (MchntBaseInfo mchnt : mchnts) {
                try {
                    stmt.setString(2, mchnt.getStreet_cd());
                    stmt.setString(3, mchnt.getStreet_nm());
                    stmt.setString(4, mchnt.getCashier_train_flag() != null ? mchnt.getCashier_train_flag() : "");
                    stmt.setString(5, mchnt.getCompetitor() != null ? mchnt.getCompetitor() : "");
                    stmt.setString(6, mchnt.getCompetitor_coupon() != null ? mchnt.getCompetitor_coupon() : "");
                    stmt.setString(7, mchnt.getCompetitor_tp() != null ? mchnt.getCompetitor_tp() : "");
                    stmt.setString(8, mchnt.getContact() != null ? mchnt.getContact() : "");
                    stmt.setString(9, mchnt.getCup_branch_ins_id_cd());
                    stmt.setString(10, mchnt.getCup_branch_nm());
                    stmt.setString(11, mchnt.getLogo_num());
                    stmt.setString(12, mchnt.getLogo_post_flag());
                    stmt.setString(13, mchnt.getMchnt_cd());
                    stmt.setString(14, mchnt.getMchnt_tp());
                    stmt.setString(15, mchnt.getNfc_flag());
                    stmt.setString(16, mchnt.getPwd_sign_flag());
                    stmt.setString(17, mchnt.getQrc_flag());
                    stmt.setString(18, mchnt.getScan_tp());
                    stmt.setString(19, mchnt.getStore_addr());

                    String storeId = mchnt.getStreet_cd() + "_" + MD5Util.MD5(mchnt.getStore_nm());

                    stmt.setString(20, storeId);
                    stmt.setString(21, mchnt.getStore_nm());
                    stmt.setString(22, mchnt.getTel() != null ? mchnt.getTel() : "");
                    stmt.setString(23, mchnt.getTerm_id());
                    stmt.setString(24, mchnt.getTrans_at_per_bill());

                    stmt.setString(1, mchnt.getStore_id() + "|" + mchnt.getMchnt_cd() + "|" + mchnt.getTerm_id());
                    logger.info("excute sql=" + sql);
                    logger.info("row_key=" + mchnt.getMchnt_cd() + "_" + mchnt.getTerm_id() + "biz_street_cd=" + mchnt.getStreet_cd() + "biz_street_nm=" + mchnt.getStreet_nm() + "Store_id=" + mchnt.getStore_id() + "cup_branch_ins_id_cd=" + mchnt.getCup_branch_ins_id_cd() + "cup_branch_nm=" + mchnt.getCup_branch_nm() + "logo_num=" + mchnt.getLogo_num() + "logo_post_flag=" + mchnt.getLogo_post_flag() + "mchnt_cd=" + mchnt.getMchnt_cd() + "mchnt_tp=" + mchnt.getMchnt_tp() + "nfc_flag=" + mchnt.getNfc_flag() + "pwd_sign_flag=" + mchnt.getPwd_sign_flag() + "qrc_flag=" + mchnt.getQrc_flag() + "scan_tp=" + mchnt.getScan_tp() + "stroe_addr=" + mchnt.getStore_addr() + "store_nm=" + mchnt.getStore_nm() + "term_id=" + mchnt.getTerm_id());

                    stmt.execute();
                } catch (Exception e) {
                    logger.error("error in insert street info", e);
                    result.add(mchnt);
                }

            }
        } catch (SQLException e) {
            logger.error("error in insert street info", e);
            throw new RuntimeException("error in insert street info");
        } finally {
            impalaJdbc.close(stmt);
            impalaJdbc.close(conn);
        }

        return result;

    }

}
